﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ClassFolio;
using System.Data.OracleClient;


namespace OraFolio.Gates
{
    using System.Data;
    using System.Globalization;
    using ClassFolio.Model;
    
    public class OraUserGate : IUserGate
    {
        public bool UserExist(int userId)
        {
            OraDB.CheckConnection();
            string sql = " select count(*) cnt from users u where id = :1";
            OracleCommand cmd = new OracleCommand(sql, OraDB.Connection, OraDB.Transaction);
            cmd.Parameters.Add("1", OracleType.Number).Direction = ParameterDirection.Input;
            cmd.Parameters["1"].Value = userId;

            OracleDataReader reader = cmd.ExecuteReader();
            try
            {
                if (reader.Read())
                    return Convert.ToInt32(reader["cnt"], CultureInfo.InvariantCulture) == 1;
                else
                    return false;
            }
            catch (Exception err)
            {
                throw new EFolioSource(0, MessageType.AllertLog, Messages.um_user_exist, err); 
            }
            finally
            {
                reader.Close();
            }
        }
        public void RemoveUser(int activeUserId, int userId)
        {
            OraDB.CheckConnection();
            string sql = "begin folio.users_pkg.user_del(:1, :2); end;";
            OracleCommand cmd = new OracleCommand(sql, OraDB.Connection, OraDB.Transaction);
            cmd.Parameters.Add("1", OracleType.Number).Direction = ParameterDirection.Input;
            cmd.Parameters["1"].Value = activeUserId;
            cmd.Parameters.Add("2", OracleType.Number).Direction = ParameterDirection.Input;
            cmd.Parameters["2"].Value = userId;
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception err)
            {
                throw new EFolioSource(0, MessageType.AllertLog, Messages.um_del, err); 
            }
        }
        public User ApplyToDb(int activeUserId, User user)
        {
			if (user.ID == 0)
				return UserInsert(activeUserId, user);
			else
				return UserUpdate(activeUserId, user);
        }
        public void RefreshFromDb(User user)
        {
            OraDB.CheckConnection();
            string sql = " select name, patname, surname, login, active  from folio.users u where id = :1";
            OracleCommand cmd = new OracleCommand(sql, OraDB.Connection, OraDB.Transaction);
            cmd.Parameters.Add("1", OracleType.Number).Direction = ParameterDirection.Input;
            cmd.Parameters["1"].Value = user.ID;

            OracleDataReader reader = cmd.ExecuteReader();
            try
            {
                while (reader.Read())
                {
                    user.Name = reader["name"].ToString();
                    user.Patname = reader["patname"].ToString();
                    user.Surname = reader["surname"].ToString();
                    user.UserLogin = reader["login"].ToString();
                    user.IsActive = Convert.ToInt32(reader["active"], CultureInfo.InvariantCulture) == 1;
                }
            }
            catch (Exception err)
            {
                throw new EFolioSource(0, MessageType.AllertLog, Messages.um_refresh, err);
            }
            finally
            {
                reader.Close();
            }
        }
		public List<UsersAction> GetUsersAction(int userId)
		{
			OraDB.CheckConnection();
			string sql = " select t.id, t.name from table(folio.safety_pkg.user_rights(:1)) t";
			OracleCommand cmd = new OracleCommand(sql, OraDB.Connection, OraDB.Transaction);
			cmd.Parameters.Add("1", OracleType.Number).Direction = ParameterDirection.Input;
			cmd.Parameters["1"].Value = userId;

			OracleDataReader reader = cmd.ExecuteReader();
			try
			{
				List<UsersAction> usersAction = new List<UsersAction>();
				while (reader.Read())
				{
					usersAction.Add(
						new UsersAction(Convert.ToInt32(reader.GetValue(0), CultureInfo.InvariantCulture), 
							reader.GetValue(1).ToString()));
				}
				return usersAction;
			}
			catch (Exception err)
			{
				throw new EFolioSource(0, MessageType.SilentLog, Messages.um_not_load_user_rights, err);
			}
			finally
			{
				reader.Close();
			}
		}
		public List<int> GetRoleList(int userId)
		{
			OraDB.CheckConnection();
			string sql = " select id, name from roles where id in (select ru.role_id from roles_users ru where ru.user_id = :1)";
			OracleCommand cmd = new OracleCommand(sql, OraDB.Connection, OraDB.Transaction);
			cmd.Parameters.Add("1", OracleType.Number).Direction = ParameterDirection.Input;
			cmd.Parameters["1"].Value = userId;

			OracleDataReader reader = cmd.ExecuteReader();
			try
			{
				List<int> roleIdList = new List<int>();
				while (reader.Read())
				{
					roleIdList.Add(Convert.ToInt32(reader.GetValue(0), CultureInfo.InvariantCulture));
				}
				return roleIdList;
			}
			catch (Exception err)
			{
				throw new EFolioSource(0, MessageType.SilentLog, Messages.um_not_load_user_rights, err);
			}
			finally
			{
				reader.Close();
			}
		}
        public bool SetActive(int activeUserId, int userId, bool isActive)
        {
            OraDB.CheckConnection();
            string sql = " begin :1 := folio.users_pkg.user_set_active(:2, :3, :4); end";
            OracleCommand cmd = new OracleCommand(sql, OraDB.Connection, OraDB.Transaction);
            cmd.Parameters.Add("1", OracleType.Number).Direction = ParameterDirection.Output;
            cmd.Parameters.Add("2", OracleType.Number).Direction = ParameterDirection.Input;
            cmd.Parameters["2"].Value = activeUserId;
            cmd.Parameters.Add("3", OracleType.Number).Direction = ParameterDirection.Input;
            cmd.Parameters["3"].Value = userId;
            cmd.Parameters.Add("4", OracleType.Number).Direction = ParameterDirection.Input;
            if (isActive)
                cmd.Parameters["4"].Value = 1;
            else
                cmd.Parameters["4"].Value = 0;

            try
            {
                cmd.ExecuteNonQuery();
                return Convert.ToInt32(cmd.Parameters["1"].Value, CultureInfo.InvariantCulture) == 1;
            }
            catch (Exception err)
            {
                throw new EFolioSource(0, MessageType.AllertLog, Messages.um_set_active, err); 
            }
        }
        public void AssignRole(int activeUserId, int userId, int roleId)
        {
            OraDB.CheckConnection();
            string sql = "begin folio.users_pkg.user_set_role(:1, :2, :3); end;";
            OracleCommand cmd = new OracleCommand(sql, OraDB.Connection, OraDB.Transaction);
            cmd.Parameters.Add("1", OracleType.Number).Direction = ParameterDirection.Input;
            cmd.Parameters["1"].Value = activeUserId;
            cmd.Parameters.Add("2", OracleType.Number).Direction = ParameterDirection.Input;
            cmd.Parameters["2"].Value = userId;
            cmd.Parameters.Add("3", OracleType.Number).Direction = ParameterDirection.Input;
            cmd.Parameters["3"].Value = roleId;
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception err)
            {
                throw new EFolioSource(0, MessageType.AllertLog, Messages.um_assign_role, err);
            }
        }
        public void RemoveRole(int activeUserId, int userId, int roleId)
        {
            OraDB.CheckConnection();
        	string sql = "begin folio.users_pkg.user_remove_role(:1, :2, :3); end;";
            OracleCommand cmd = new OracleCommand(sql, OraDB.Connection, OraDB.Transaction);
            cmd.Parameters.Add("1", OracleType.Number).Direction = ParameterDirection.Input;
            cmd.Parameters["1"].Value = activeUserId;
            cmd.Parameters.Add("2", OracleType.Number).Direction = ParameterDirection.Input;
            cmd.Parameters["2"].Value = userId;
            cmd.Parameters.Add("3", OracleType.Number).Direction = ParameterDirection.Input;
            cmd.Parameters["3"].Value = roleId;
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception err)
            {
                throw new EFolioSource(0, MessageType.AllertLog, Messages.um_remove_role, err);
            }
        }
        public void AllowAction(int activeUserId, int userId, int actionId)
        {
            OraDB.CheckConnection();
	        string sql = "begin folio.users_pkg.user_allow_right(:1, :2, :3); end;";
            OracleCommand cmd = new OracleCommand(sql, OraDB.Connection, OraDB.Transaction);
            cmd.Parameters.Add("1", OracleType.Number).Direction = ParameterDirection.Input;
            cmd.Parameters["1"].Value = activeUserId;
            cmd.Parameters.Add("2", OracleType.Number).Direction = ParameterDirection.Input;
            cmd.Parameters["2"].Value = userId;
            cmd.Parameters.Add("3", OracleType.Number).Direction = ParameterDirection.Input;
            cmd.Parameters["3"].Value = actionId;
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception err)
            {
                throw new EFolioSource(0, MessageType.AllertLog, Messages.um_allow_right_user, err);
            }
        }
        public void BanAction(int activeUserId, int userId, int actionId)
        {
            OraDB.CheckConnection();
	        string sql = "begin folio.users_pkg.user_ban_right(:1, :2, :3); end;";
            OracleCommand cmd = new OracleCommand(sql, OraDB.Connection, OraDB.Transaction);
            cmd.Parameters.Add("1", OracleType.Number).Direction = ParameterDirection.Input;
            cmd.Parameters["1"].Value = activeUserId;
            cmd.Parameters.Add("2", OracleType.Number).Direction = ParameterDirection.Input;
            cmd.Parameters["2"].Value = userId;
            cmd.Parameters.Add("3", OracleType.Number).Direction = ParameterDirection.Input;
            cmd.Parameters["3"].Value = actionId;
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception err)
            {
                throw new EFolioSource(0, MessageType.AllertLog, Messages.um_ban_right_user, err);
            }
        }
        public void RemoveAction(int activeUserId, int userId, int actionId)
        {
            OraDB.CheckConnection();
	        string sql = "begin folio.users_pkg.user_remove_right(:1, :2, :3); end;";
            OracleCommand cmd = new OracleCommand(sql, OraDB.Connection, OraDB.Transaction);
            cmd.Parameters.Add("1", OracleType.Number).Direction = ParameterDirection.Input;
            cmd.Parameters["1"].Value = activeUserId;
            cmd.Parameters.Add("2", OracleType.Number).Direction = ParameterDirection.Input;
            cmd.Parameters["2"].Value = userId;
            cmd.Parameters.Add("3", OracleType.Number).Direction = ParameterDirection.Input;
            cmd.Parameters["3"].Value = actionId;
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception err)
            {
                throw new EFolioSource(0, MessageType.AllertLog, Messages.um_remove_right_user, err);
            }
        }

        public Sequence GetSequence(SqlSwitch swth)
        {
            Sequence sequence = new Sequence();
            sequence.Sql = " SELECT NMB, ID, NAME, SURNAME FROM V_USERS WHERE 1=1";
			List<List<string>> fields = new List<List<string>>() {
                new List<string>() {"ID", FieldsName.user_id}, 
				new List<string>() {"NAME", FieldsName.user_name},
				new List<string>() {"SURNAME", FieldsName.user_surname}
            };

            if (swth != null)
                sequence.SqlSwitch.Condition = 
					swth.Condition.Where(i => fields.Select(res => res[0]).Contains(i.Name.ToUpper())).ToList();

			sequence.Caption = fields.Select(res => res[1]).ToList();
			sequence.Index = 0;
            sequence.RecordCount = Adjustment.Default.CountRecordActors;

            return sequence;
        }
		public Sequence GetQuickFindSequence(SqlSwitch swth, string filter)
		{
			Sequence sequence = new Sequence();
			sequence.Sql = String.Format(
				" SELECT ID, NAME, SURNAME FROM V_USERS WHERE ID IN "+
				" (SELECT UH.ID FROM USERS_HASH UH WHERE UH.HASH LIKE '%{0}%')", filter);
			List<List<string>> fields = new List<List<string>>() {
                new List<string>() {"ID", FieldsName.user_id}, 
				new List<string>() {"NAME", FieldsName.user_name},
				new List<string>() {"SURNAME", FieldsName.user_surname}
            };

			if (swth != null)
				sequence.SqlSwitch.Condition = swth.Condition.Where(i => fields.Select(res => res[0]).Contains(i.Name.ToUpper())).ToList();

			sequence.Caption = fields.Select(res => res[1]).ToList();
			sequence.Index = 0;
			sequence.RecordCount = Adjustment.Default.CountRecordActors;

			return sequence;
		}

		private static User UserInsert(int activeUserId, User user)
		{
			OraDB.CheckConnection();
			string sql = "begin :1 := folio.users_pkg.user_ins(:2, :3, :4, :5, :6, :7, :8); end;";
			OracleCommand cmd = new OracleCommand(sql, OraDB.Connection, OraDB.Transaction);
			cmd.Parameters.Add("1", OracleType.Number).Direction = ParameterDirection.Output;
			cmd.Parameters.Add("2", OracleType.Number).Direction = ParameterDirection.Input;
			cmd.Parameters["2"].Value = activeUserId;
			cmd.Parameters.Add("3", OracleType.VarChar).Direction = ParameterDirection.Input;
			cmd.Parameters["3"].Value = user.Name;
			cmd.Parameters.Add("4", OracleType.VarChar).Direction = ParameterDirection.Input;
			cmd.Parameters["4"].Value = user.Patname;
			cmd.Parameters.Add("5", OracleType.VarChar).Direction = ParameterDirection.Input;
			cmd.Parameters["5"].Value = user.Surname;
			cmd.Parameters.Add("6", OracleType.VarChar).Direction = ParameterDirection.Input;
			cmd.Parameters["6"].Value = user.UserLogin;
			cmd.Parameters.Add("7", OracleType.VarChar).Direction = ParameterDirection.Input;
			cmd.Parameters["7"].Value = user.Password;
			cmd.Parameters.Add("8", OracleType.Number).Direction = ParameterDirection.Input;

			if (user.IsActive)
				cmd.Parameters["8"].Value = 1;
			else
				cmd.Parameters["8"].Value = 0;
			try
			{
				cmd.ExecuteNonQuery();
				return User.CreateUserById(Convert.ToInt32(cmd.Parameters["1"].Value, CultureInfo.InvariantCulture));
			}
			catch (Exception err)
			{
				throw new EFolioSource(0, MessageType.AllertLog, Messages.um_insert, err);
			}
		}
		private static User UserUpdate(int activeUserId, User user)
		{
			OraDB.CheckConnection();
			string sql = "begin folio.users_pkg.user_upd(:1, :2, :3, :4, :5, :6, :7, :8); end;";
			OracleCommand cmd = new OracleCommand(sql, OraDB.Connection, OraDB.Transaction);
			cmd.Parameters.Add("1", OracleType.Number).Direction = ParameterDirection.Input;
			cmd.Parameters["1"].Value = activeUserId;
			cmd.Parameters.Add("2", OracleType.Number).Direction = ParameterDirection.Input;
			cmd.Parameters["2"].Value = user.ID;
			cmd.Parameters.Add("3", OracleType.VarChar).Direction = ParameterDirection.Input;
			cmd.Parameters["3"].Value = user.Name;
			cmd.Parameters.Add("4", OracleType.VarChar).Direction = ParameterDirection.Input;
			cmd.Parameters["4"].Value = user.Patname;
			cmd.Parameters.Add("5", OracleType.VarChar).Direction = ParameterDirection.Input;
			cmd.Parameters["5"].Value = user.Surname;
			cmd.Parameters.Add("6", OracleType.VarChar).Direction = ParameterDirection.Input;
			cmd.Parameters["6"].Value = user.UserLogin;
			cmd.Parameters.Add("7", OracleType.VarChar).Direction = ParameterDirection.Input;
			cmd.Parameters["7"].Value = String.IsNullOrEmpty(user.Password) ? "" : user.Password;
			cmd.Parameters.Add("8", OracleType.Number).Direction = ParameterDirection.Input;

			if (user.IsActive)
				cmd.Parameters["8"].Value = 1;
			else
				cmd.Parameters["8"].Value = 0;
			try
			{
				cmd.ExecuteNonQuery();
				return user;
			}
			catch (Exception err)
			{
				throw new EFolioSource(0, MessageType.AllertLog, Messages.um_update, err);
			}
		}
	}

}
